package com.cyh.core.generator;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.validation.constraints.NotNull;

import com.cyh.core.framework.common.utils.FileUtil;
import com.cyh.core.framework.common.utils.ReadTextUtil;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.ClassUtils;

/**
 * @Author: yanhang_chen
 * @Date: 2020-09-22 08:48:55
 */
@Component
public class DatabaseGenerator   {

    private static String DRIVER;
    @Value("${spring.datasource.driver-class-name}")
    private void setDriver(String driver){
        DRIVER = driver;
    }

    private static String DATABASE_URL;
    @Value("${spring.datasource.url}")
    private void setUrl(String url){
        DATABASE_URL = url;
    }

    private static String USERNAME;
    @Value("${spring.datasource.username}")
    private void setUsername(String username){
        USERNAME = username;
    }

    private static String PASSWORD;
    @Value("${spring.datasource.password}")
    private void setPassword(String password){
        PASSWORD = password;
    }

    private static final String SQL = "SELECT * FROM ";// 数据库操作

    /**
     * 获取数据库连接
     *
     * @return
     */
    private static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     * @param conn
     */
    private static void closeConnection(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取表中所有字段名称
     * @param tableName 表名
     * @return
     */
    private static List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     * @param tableName
     * @return
     */
    private static List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnTypes;
    }

    /**
     * 获取表中字段的所有注释
     * @param tableName
     * @return
     */
    private static List<String> getColumnComments(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        List<String> columnComments = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnComments;
    }

    public static void generatorEntity(@NotNull String packageName,@NotNull String tableName,@NotNull String outPutFilePath){
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String outPut = "";
        String upTableName = tableName.toUpperCase().substring(0,1) + tableName.substring(1,tableName.length());
        List<String> columnNames = getColumnNames(tableName);
        List<String> columnTypes = getColumnTypes(tableName);
        String separator = System.getProperty("line.separator");
        for (int i = 0; i < columnNames.size(); i++) {
            String column = columnNames.get(i);
            if ("id".equals(column.toLowerCase())){
                outPut += "    @Id" +separator+
                        "    @GeneratedValue(strategy = GenerationType.IDENTITY)"+separator;
            }
            String upColumn = column.toUpperCase().substring(0,1) + column.substring(1,column.length());
            String type = columnTypes.get(i);

            outPut += "    @Column(name = \""+column+"\")"+separator;

            switch (type){
                // 有什么类型在这里可以补充
                case "INT" :
                case "SMALLINT" : outPut += "    private Integer ";break;
                case "VARCHAR" :
                case "TEXT" : outPut += "    private String ";break;
                case "DATE" :
                case "DATETIME" : outPut += "    private Date ";break;
            }
            upColumn = replace_(upColumn);

            outPut += upColumn + ";" + separator + separator;
        }

        String text=ReadTextUtil.readFileContent(FileUtil.getRealPath( ClassUtils.getDefaultClassLoader().
                getResource("").getPath()+ "/template/template.entity"));
        text = text.replace("TABLE_NAME",tableName);
        upTableName = replace_(upTableName);
        text = text.replace("ENTITY_NAME",upTableName);
        text = text.replace("@BODY@",outPut);
        text = text.replace("PACKAGE_NAME",packageName);
        FileUtil.writeBytes(text.getBytes(),outPutFilePath +"/"+ upTableName +".java");

    }

    private static String replace_ (String str){
        while (str.indexOf("_") != -1 && str.indexOf("_") != str.length()-1){
            int index=str.indexOf("_");
            str = str.replace(
                    str.substring(index,index+2),
                    str.substring(index+1,index+2).toUpperCase());
        }
        return str;
    }

    public static void generatorEntity(@NotNull List<String> packageNames,@NotNull List<String> tableNames,@NotNull List<String> outPutFilePaths) throws Exception {
        if (packageNames.size() != outPutFilePaths.size() && outPutFilePaths.size() != tableNames.size()){
            throw new Exception("List长度不一致");
        }
        for (int i = 0; i < tableNames.size(); i++) {
            generatorEntity(packageNames.get(i),tableNames.get(i),outPutFilePaths.get(i));
        }
    }
}
